﻿using JumbotOA.DBUtility;
using Microsoft.Extensions.Configuration;
using SimpleCrawler.DependencyInjection;
using System;
using Microsoft.Extensions.DependencyInjection;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Data;
using SimpleCrawler.Entity;
using System.Reflection;
using System.Collections;
using System.Threading.Tasks;

namespace SimpleCrawler.DAL
{
    public class CrawlerDAL
    {
        private DbHelperSQLP DbHelperSQL;
        public  CrawlerDAL(DbHelperSQLP dbHelperSQL) {
            DbHelperSQL = dbHelperSQL;
        }

        #region ado
        public async Task<SqlDataReader> GetAllUrlByReader()
        {
            return await DbHelperSQL.ExecuteReader("select distinct SourceUrl  from WD_Question ");   
        }
        public async Task<DataSet> GetAllUrl()
        {
            return await DbHelperSQL.Query("select distinct SourceUrl  from WD_Question ");
        }

        public async Task<int> AddInformation(Information info)
        {
            SqlParameter[] para = new SqlParameter[] {
                 new SqlParameter("@Title", SqlDbType.NVarChar),
                 new SqlParameter("@SourceSite", SqlDbType.NVarChar),
                 new SqlParameter("@PublishDate", SqlDbType.DateTime)
             };
            para[0].Value = info.Title;
            para[1].Value = info.SourceSite;
            para[2].Value = info.PublishDate;
            int item_id = 0;
            try
            {
                //SCOPE_IDENTITY()
                item_id = await DbHelperSQL.ExecuteSqlAsync("insert into information(Title,SourceSite,PublishDate) values(@Title,@SourceSite,@PublishDate)", para);
            }
            catch (Exception ex)
            {
                //TDLib.Tools.Log.WriteErr("向服务器录入数据：" + ex.Message);
                //return -1;
            }
            return item_id;
        }
        public async Task<int> AddQuestion(WD_Question question)
        {
            //SqlParameter[] para = new SqlParameter[] {
            //     new SqlParameter("@Question", SqlDbType.NVarChar),
            //     new SqlParameter("@Description", SqlDbType.NVarChar),
            //     new SqlParameter("@CreateTime", SqlDbType.DateTime),
            //     new SqlParameter("@ShowCount", SqlDbType.DateTime),
            //     new SqlParameter("@SourceSite", SqlDbType.DateTime),
            //     new SqlParameter("@SourceUrl", SqlDbType.DateTime),
            //     new SqlParameter("@KeyWord", SqlDbType.DateTime),
            // };
            //para[0].Value = question.Question;
            //para[1].Value = question.Description;
            //para[2].Value = question.CreateTime;
            //para[2].Value = question.CreateTime;
            //para[2].Value = question.CreateTime;
            //para[2].Value = question.CreateTime;

            int item_id = 0;
            //try
            //{
            //    item_id = (int)await DbHelperSQL.GetSingle("insert into information(Title,SourceSite,PublishDate) values(@Title,@SourceSite,@PublishDate) select SCOPE_IDENTITY()", para);
            //}
            //catch (Exception ex)
            //{
            //    //TDLib.Tools.Log.WriteErr("向服务器录入数据：" + ex.Message);
            //    //return -1;
            //}
            return item_id;
        }


        public async Task<(int Count,string Content)> AddQuestions(List<WD_Question> questions)
        {
            int count=0;
            //固定类插入
            List<WD_Answer> answers = new List<WD_Answer>();
            StringBuilder sb = new StringBuilder();
            foreach (WD_Question question in questions)
            {
                SqlParameter[] para = new SqlParameter[] {
                 new SqlParameter("@Question", SqlDbType.NVarChar),
                 new SqlParameter("@CreateTime", SqlDbType.DateTime),
                 new SqlParameter("@ShowCount", SqlDbType.Int),
                 new SqlParameter("@SourceSite", SqlDbType.Int),
                 new SqlParameter("@SourceUrl", SqlDbType.NVarChar),
                 new SqlParameter("@KeyWord", SqlDbType.NVarChar),
             };
                para[0].Value = question.Question;
                para[1].Value = question.CreateTime;
                para[2].Value = question.ShowCount;
                para[3].Value = question.SourceSite;
                para[4].Value = question.SourceUrl;
                para[5].Value = question.KeyWord;

                int item_id = 0;
                try
                {
                   int.TryParse((await DbHelperSQL.GetSingle("insert into WD_Question(Question,CreateTime,ShowCount,SourceSite,SourceUrl,KeyWord)" +
                        " values(@Question,@CreateTime,@ShowCount,@SourceSite,@SourceUrl,@KeyWord) select SCOPE_IDENTITY()", para)).ToString(),out item_id);
                    count++;
                }
                catch (Exception ex)
                {
                    sb.AppendLine($"Question Insert Error:{question.SourceUrl}");
                }
                if (item_id > 0)
                {
                    foreach (WD_Answer answer in question.Answers)
                    {
                        answer.QID = item_id;
                        answers.Add(answer);
                    }
                }
            }
            //通用类插入 反射
            //var sender= GetSqlInfo<WD_Question>(questions);
            //for (int i = 0; i < sender.paras.Count; i++)
            //{
            //    try
            //    {
            //        int item_id = (int)await DbHelperSQL.GetSingle(sender.sqlText, sender.paras[i]);
            //        foreach (WD_Answer answer in questions[i].Answers)
            //        {
            //            answer.QID = item_id;
            //            answers.Add(answer);
            //        }
            //    }
            //    catch (Exception ex)
            //    {

            //    }
            //}
            try {
                if(answers.Count>0)
                {
                    await AddInfoList<WD_Answer>(answers).ConfigureAwait(false);
                }             
            }
            catch (Exception ex)
            {
                sb.AppendLine($"Question Insert Error:批量插入答案失败!");
            }

            return (count,sb.ToString());
        }
        /// <summary>
        /// 将对象映射成对应的sql
        /// </summary>
        private (string sqlText,List<SqlParameter[]> paras) GetSqlInfo<T>(IList<T> list, string autoCol = "ID")
        {
            List<SqlParameter[]> paras = new List<SqlParameter[]>();
            StringBuilder part1 = new StringBuilder();
            part1.Append($"insert into {typeof(T).Name}(");
            StringBuilder part2 = new StringBuilder();
            part2.Append($" values(");
            if (list.Count > 0)
            {
                PropertyInfo[] propertys = list[0].GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    if (pi.PropertyType.IsClass ||pi.Name==autoCol )
                    {
                        continue;
                    }
                    else
                    {
                        part1.Append(pi.Name);
                        part2.Append($"@{pi.Name}");
                    }
                }
                part1.Append(")");
                part2.Append(") select SCOPE_IDENTITY()");


                for (int i=0; i<list.Count; i++)
                {
                    object t = list[i];
                    List<SqlParameter> tempList = new List<SqlParameter>();
                    foreach (PropertyInfo pi in propertys)
                    {
                        if (pi.PropertyType.IsClass || pi.Name == autoCol)
                        {
                            continue;
                        }
                        else
                        {
                            SqlParameter para = new SqlParameter($"@{pi.Name}", pi.GetValue(t, null));
                            tempList.Add(para);
                        }
                    }
                    paras.Add(tempList.ToArray());
                }
            }
            return (part1.ToString()+part2.ToString(),paras);
        }
        #endregion


        #region bulkcopy
        public async Task<bool> AddInfoList<T>(List<T> list)
        {
           DataTable dt = ToDataTable<T>(list);
           dt.TableName = "dbo."+typeof(T).Name;
           await DbHelperSQL.InsertByDataTable(dt).ConfigureAwait(false);
           return true;     
        }
        /// <summary>
        /// 基础属性映射成表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="autoCol"></param>
        /// <returns></returns>
        private  DataTable ToDataTable<T>(IList<T> list,string autoCol="ID")
        {
            DataTable result = new DataTable();
            if (list.Count > 0)
            {
                PropertyInfo[] propertys = list[0].GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    if (pi.PropertyType.IsGenericType)
                    {
                        if (pi.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
                        {
                            if (pi.PropertyType.GetGenericArguments()[0] == typeof(DateTime))
                            {
                                result.Columns.Add(pi.Name, pi.PropertyType.GetGenericArguments()[0]);
                            }
                        }
                    }
                    else
                        result.Columns.Add(pi.Name, pi.PropertyType);
                }
                foreach (object t in list)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        if (pi.Name == autoCol)
                        {
                            tempList.Add(null);
                        }
                        else
                        {
                            object obj = pi.GetValue(t, null);
                            //if (obj == null)
                            //    tempList.Add(DBNull.Value);
                            //else
                            tempList.Add(obj);
                        }
                    }
                    object[] array = tempList.ToArray();
                    result.LoadDataRow(array,true);
                }
            }
            return result;
        }
        #endregion
    }
}
